﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace DAO
{
    public class PersonalDAO
    {
        public static DataTable LayTatCaKHCaNhan()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }


                rs = SqlHelper.ExecuteDataset(connect, CommandType.StoredProcedure, "usp_GetAllPersonal").Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static DataTable LayKHCaNhanTheoNhanVien(Int32 MaNV)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@manv", MaNV);

                rs = SqlHelper.ExecuteDataset(connect, CommandType.StoredProcedure, "usp_GetAllPersonalById", param).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static int ThemKHCaNhan(Entity.Personal kh)
        {
            SqlConnection connect = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[18];
                param[0] = new SqlParameter("@makhcanhan", kh.Ma);
                param[1] = new SqlParameter("@tenkhcanhan", kh.Ten);
                param[2] = new SqlParameter("@diachi", kh.DiaChi);
                param[3] = new SqlParameter("@hokhau", kh.HoKhau);
                param[4] = new SqlParameter("@ngaysinh", kh.NgaySinh);
                param[5] = new SqlParameter("@cmnd", kh.CMND);
                param[6] = new SqlParameter("@dienthoai", kh.DienThoai);
                param[7] = new SqlParameter("@masothue", kh.MaSoThue);
                param[8] = new SqlParameter("@nghenghiep", kh.NgheNghiep);
                param[9] = new SqlParameter("@noilamviec", kh.NoiLamViec);
                param[10] = new SqlParameter("@thunhapbinhquan", kh.ThuNhap);
                param[11] = new SqlParameter("@honnhan", kh.HonNhan);
                param[12] = new SqlParameter("@thunhapgiadinh", kh.ThuNhapGiaDinh);
                param[13] = new SqlParameter("@soluongphuthuoc", kh.SoNguoiPhuThuoc);
                param[14] = new SqlParameter("@trinhdo", kh.TrinhDo);
                param[15] = new SqlParameter("@sonamlamviec", kh.SoNamLamViec);
                param[16] = new SqlParameter("@hanmuc", kh.HanMuc);
                param[17] = new SqlParameter("@manhanvien", kh.MaNhanVien);

                return SqlHelper.ExecuteNonQuery(connect, CommandType.StoredProcedure, "usp_ThemKHCaNhan", param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }
        public static int CapNhatKHCaNhan(Entity.Personal kh)
        {
            SqlConnection connect = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[18];
                param[0] = new SqlParameter("@makhcanhan", kh.Ma);
                param[1] = new SqlParameter("@tenkhcanhan", kh.Ten);
                param[2] = new SqlParameter("@diachi", kh.DiaChi);
                param[3] = new SqlParameter("@hokhau", kh.HoKhau);
                param[4] = new SqlParameter("@ngaysinh", kh.NgaySinh);
                param[5] = new SqlParameter("@cmnd", kh.CMND);
                param[6] = new SqlParameter("@dienthoai", kh.DienThoai);
                param[7] = new SqlParameter("@masothue", kh.MaSoThue);
                param[8] = new SqlParameter("@nghenghiep", kh.NgheNghiep);
                param[9] = new SqlParameter("@noilamviec", kh.NoiLamViec);
                param[10] = new SqlParameter("@thunhapbinhquan", kh.ThuNhap);
                param[11] = new SqlParameter("@honnhan", kh.HonNhan);
                param[12] = new SqlParameter("@thunhapgiadinh", kh.ThuNhapGiaDinh);
                param[13] = new SqlParameter("@soluongphuthuoc", kh.SoNguoiPhuThuoc);
                param[14] = new SqlParameter("@trinhdo", kh.TrinhDo);
                param[15] = new SqlParameter("@sonamlamviec", kh.SoNamLamViec);
                param[16] = new SqlParameter("@hanmuc", kh.HanMuc);
                param[17] = new SqlParameter("@manhanvien", kh.MaNhanVien);

                return SqlHelper.ExecuteNonQuery(connect, CommandType.StoredProcedure, "usp_CapNhatKHCaNhan", param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }
        public static int XoaKHCaNhan(String kh)
        {
            SqlConnection connect = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@makhcanhan", kh);
                return SqlHelper.ExecuteNonQuery(connect, CommandType.StoredProcedure, "usp_XoaKHCaNhan", param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }
    }
}
